package dataAccessLayer.factoryMySQL.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dataAccessLayer.abstractFactory.daos.DAOsales;
import dataAccessLayer.dataBase.DBManager;
import dataAccessLayer.visualObject.VOsale;

public class DAOsalesMySQL implements DAOsales
{
	private DBManager dbManager;
	
	public DAOsalesMySQL(DBManager dbManager) 
	{
		this.dbManager = dbManager;
	}

	public int insertSale(VOsale vOsale) 
	{
		int code = -1;
		
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("INSERT INTO sales (date, totalValue, seller, customer) VALUES (?,?,?,?)");
			
			preparedStatement.setString(1, vOsale.getDate());
			preparedStatement.setInt(2, vOsale.getTotalValue());
			preparedStatement.setInt(3, vOsale.getSeller());
			preparedStatement.setInt(4, vOsale.getCustomer());
			
			preparedStatement.execute();
			
			preparedStatement = connection.prepareStatement("SELECT code FROM sales ORDER BY code DESC LIMIT 1");
			
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			
			code = resultSet.getInt(1);
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return code;
	}

	public boolean updateSale(VOsale vOsale) 
	{
		boolean updated = false;
		
		PreparedStatement preparedStatement = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("UPDATE sales SET date=?, totalValue=?, seller=?, customer=? WHERE code=?");
			
			preparedStatement.setString(1, vOsale.getDate());
			preparedStatement.setInt(2, vOsale.getTotalValue());
			preparedStatement.setInt(3, vOsale.getSeller());
			preparedStatement.setInt(4, vOsale.getCustomer());
			preparedStatement.setInt(5, vOsale.getCode());
			
			preparedStatement.execute();
			
			updated = true;
			
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return updated;
	}

	public boolean removeSale(VOsale vOsale)
	{
		boolean removed = false;
		
		PreparedStatement preparedStatement = null;
		
		Connection connection = dbManager.getConnection();
		
		try
		{
			preparedStatement = connection.prepareStatement("delete from sales where code=?");
			
			preparedStatement.setInt(1, vOsale.getCode());
			preparedStatement.execute();
			
			removed = true;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return removed;
	}
	
	public ArrayList<VOsale> listSales()
	{
		ArrayList<VOsale>sales = new ArrayList<VOsale>();
		
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,date, totalValue, seller, customer FROM sales");
			
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next())
			{
				VOsale sale = new VOsale();
				sale.setCode(resultSet.getInt(1));
				sale.setDate(resultSet.getString(2));
				sale.setTotalValue(resultSet.getInt(3));
				sale.setSeller(resultSet.getInt(4));
				sale.setCustomer(resultSet.getInt(5));
				
				sales.add(sale);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return sales;
	}

	public VOsale searchSaleByCode(VOsale vOsale) 
	{ 
		VOsale sale = null;
		
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		Connection connection = dbManager.getConnection();
		
		try 
		{
			preparedStatement = connection.prepareStatement("SELECT code,date, totalValue, seller, customer FROM sales WHERE code=?");
			
			preparedStatement.setInt(1, vOsale.getCode());
		
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			
			sale = new VOsale();
			sale.setCode(resultSet.getInt(1));
			sale.setDate(resultSet.getString(2));
			sale.setTotalValue(resultSet.getInt(3));
			sale.setSeller(resultSet.getInt(4));
			sale.setCustomer(resultSet.getInt(5));
			
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return sale;
	}
	
}
